package com.shetuan.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.shetuan.bean.Login;
import com.shetuan.bean.Member;
import com.shetuan.util.Page;

/**
 * 成员的增删改查
 * * @author Administrator
 */
public class MemberDao extends BaseDao {
    /**
     * 获取成员列表，系统管理员，查询的是全部成员的信息
     * * @param page
     * @return
     */
    public List<Member> getMembersForPage(Page page) {

        List<Member> members = new ArrayList<Member>();
        try {
            // 获取连接
            getCon();
            // 为page分页，查询总记录数
            // [FIXED] 保持只查询member表
            String countsql = "select count(1) from member ";
            // 执行SQL
            exeQuery(countsql);
            // 获取结果集
            ResultSet countResultSet = (ResultSet) ps.executeQuery();
            // 如果结果集存在，获取该字段，将其赋值给itemCont
            if (countResultSet.next()) {
                int count = countResultSet.getInt(1);
                page.setItemCont(count);
            }
            // 查询成员的详细信息

            // [FIXED] 使用 LEFT JOIN 替换 INNER JOIN，并从 member 表(m)获取 manager_id
            String sql = "select m.login_name, m.member_name,m.member_gender,m.manager_id,m.member_grade,m.member_institute,m.member_phone,m.member_email, c.community_name "
                    + "from member as m "
                    + "LEFT JOIN community as c ON m.join_communityId=c.community_id "
                    + "limit ?,?";

            //// 执行sql ，传入分页的起始页，每页大小
            exeQuery(sql, page.getItemStart(), page.getPageSize());
            // 获取结果集
            ResultSet rs = (ResultSet) ps.executeQuery();
            // 如果结果集存在，获取对应字段将其赋值给一个新的member对象，并将其添加到members集合中
            while (rs.next()) {
                Member member = new Member();
                member.setLoginName(rs.getString("login_name"));
                member.setMemberName(rs.getString("member_name"));
                member.setMemberGender(rs.getString("member_gender"));
                member.setManagerId(rs.getInt("manager_id"));
                member.setMemberGrade(rs.getString("member_grade"));
                member.setMemberInstitute(rs.getString("member_institute"));
                member.setMemberPhone(rs.getString("member_phone"));
                member.setMemberEmail(rs.getString("member_email"));
                // [FIXED] 恢复社团名称的设置
                member.setJoinCommunity(rs.getString("community_name"));
                //将member添加到集合中
                members.add(member);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            //关闭所有连接
            closeAll();
        }
        return members;
    }


    /**
     * 社团管理员查找本社团的成员
     * @param page
     * @param loginName
     * @return
     */
    public List<Member> getMembersForPage(Page page, String loginName){
        List<Member> members = new ArrayList<Member>();
        try {
            // 获取连接
            getCon();
            // 为page分页，查询总记录数
            String countsql = "select count(1) from member as m where m.join_communityId IN (select join_communityId from member as m1 where m1.login_name = ?)";
            //String countsql = "select count(1) from member ";
            // 执行SQL
            exeQuery(countsql, loginName);
            // 获取结果集
            ResultSet countResultSet = (ResultSet) ps.executeQuery();
            // 如果结果集存在，获取该字段，将其赋值给itemCont
            if (countResultSet.next()) {
                int count = countResultSet.getInt(1);
                page.setItemCont(count);
            }
            // 查询成员的详细信息

            // [FIXED] 使用 LEFT JOIN 替换 INNER JOIN
            String sql = "select m.login_name, m.member_name,m.member_gender,m.manager_id,m.member_grade,m.member_institute,m.member_phone,m.member_email, c.community_name "
                    + "from member as m "
                    + "LEFT JOIN community as c ON m.join_communityId=c.community_id "
                    + "where m.join_communityId IN (select join_communityId from member as m1 where m1.login_name = ?) limit ?,?";

            //// 执行sql ，传入分页的起始页，每页大小
            exeQuery(sql,loginName, page.getItemStart(), page.getPageSize());
            // 获取结果集
            ResultSet rs = (ResultSet) ps.executeQuery();
            // 如果结果集存在，获取对应字段将其赋值给一个新的member对象，并将其添加到members集合中
            while (rs.next()) {
                Member member = new Member();
                member.setLoginName(rs.getString("login_name"));
                member.setMemberName(rs.getString("member_name"));
                member.setMemberGender(rs.getString("member_gender"));
                member.setManagerId(rs.getInt("manager_id"));
                member.setMemberGrade(rs.getString("member_grade"));
                member.setMemberInstitute(rs.getString("member_institute"));
                member.setMemberPhone(rs.getString("member_phone"));
                member.setMemberEmail(rs.getString("member_email"));
                // [FIXED] 恢复社团名称的设置
                member.setJoinCommunity(rs.getString("community_name"));
                //将member添加到集合中
                members.add(member);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            //关闭所有连接
            closeAll();
        }
        return members;
    }
    /**
     * 根据登录名名获取成员对象
     * @param loginname
     * @return
     */
    public Member getMemberByName(String loginname) {
        Member member = new Member();
        try {
            //获取连接
            getCon();
            // [FIXED] 使用 LEFT JOIN 替换 INNER JOIN
            // [MODIFIED] 添加 m.join_communityId 和 m.creat_community 到查询
            String sql = "select l.login_name,m.member_name,m.member_gender,l.manager_id,m.member_grade,m.member_institute,m.member_add,m.member_phone,m.member_email,c.community_name, m.join_communityId, m.creat_community "
                    + "FROM member as m "
                    + "INNER JOIN login as l ON m.login_name=l.login_name "
                    + "LEFT JOIN community as c ON m.join_communityId=c.community_id "
                    + "where l.login_name=?";
            //执行查询，并传入参数：登录名
            exeQuery(sql, loginname);
            //获取结果集
            ResultSet rs = (ResultSet) ps.executeQuery();
            //如果结果集存在，获取对应的字段将其赋值都给member对象
            while (rs.next()) {
                member.setLoginName(rs.getString("login_name"));
                member.setMemberName(rs.getString("member_name"));
                member.setMemberGender(rs.getString("member_gender"));
                member.setManagerId(rs.getInt("manager_id"));
                member.setMemberGrade(rs.getString("member_grade"));
                member.setMemberInstitute(rs.getString("member_institute"));
                member.setMemberAdd(rs.getString("member_add"));
                member.setMemberPhone(rs.getString("member_phone"));
                member.setMemberEmail(rs.getString("member_email"));
                member.setJoinCommunity(rs.getString("community_name"));
                member.setIsJoinCommunity(rs.getInt("join_communityId")); // [NEW] 设置社团ID
                member.setIscreatCommunity(rs.getInt("creat_community")); // [NEW] 设置是否创建
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            //关闭所有连接
            closeAll();
        }
        return member;
    }

    /**
     * 主页中的注册
     * @param login
     * @param member
     * @return
     */
    public String addIndexLogin(Login login, Member member) {
        String judge = null;
        try {
            //获取连接
            Connection con = getCon();
            //根据登录名获取登录对象
            LoginDao logindao = new LoginDao();
            Login l = logindao.getLoginByName(login.getLoginName());
            if (l == null) {//判断该登录名是否可用
                try {
                    //设置不自动提交
                    con.setAutoCommit(false);
                    //要插入到login  和  member  的 SQL
                    String sql = "insert into login(login_name,login_pass,manager_id) values(?,?,0)";
                    String sql1 = "insert into member(login_name,member_name,member_gender,member_email,member_phone,member_grade,member_institute,member_add,join_communityId,manager_id)values(?,?,?,?,?,?,?,?,0,0)";
                    //获取SQL执行结果
                    int temp = exeUpdate(sql, login.getLoginName(), login.getLoginPass());
                    int temp1 = exeUpdate(sql1, login.getLoginName(), member.getMemberName(), member.getMemberGender(), member.getMemberEmail(), member.getMemberPhone(), member.getMemberGrade(), member.getMemberInstitute(), member.getMemberAdd());
                    //如果两条SQL都执行成功，则提交，否自回滚
                    if (temp > 0 && temp1 > 0) {// 都执行成功
                        //提交事务
                        con.commit();
                        //设置为自动提交
                        con.setAutoCommit(true);
                        judge = "success";
                    } else {//有失败的信息
                        //回滚事务，不提交
                        con.rollback();
                        //设为自动提交
                        con.setAutoCommit(true);
                        judge = "fail";
                    }
                } catch (Exception e) {
                    judge = "fail";
                } finally {
                    //关闭所有连接
                    closeAll();
                }
            } else {//该登录名已经存在，不能注册，退出
                judge = "exist";
            }
        } catch (SQLException e) {
            System.out.println("MemberDao addIndexLogin is wrong");
            judge = "fail";
        }
        return judge;
    }

    /**
     * 添加成员
     * @param member
     * @return
     */
    public int addMember(Member member) {
        int msg = 0;
        try {
            //获取连接
            getCon();
            //要执行的插入SQL语句
            String sql = "insert into member(member_name,member_gender,member_grade,member_add,member_email,member_phone,creat_community,join_communityId) values(?,?,?,?,?,?,?,?)";
            //执行SQL，并传参数，并获手影响行数
            msg = exeUpdate(sql, member.getMemberName(), member.getMemberGender(),
                    member.getMemberGrade(), member.getMemberAdd(), member.getMemberEmail(), member.getMemberPhone(),
                    member.getIscreatCommunity(), member.getIsJoinCommunity());
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            //关闭所有连接
            closeAll();
        }
        return msg;
    }

    /**
     * 根据登录名删除成员
     * @param loginName
     * @return
     */
    public int deleteMember(String  loginName) {
        int result = 0;
        try {
            //获取连接
            getCon();
            //要执行的删除SQL
            String sql = "delete from member where login_name=?";
            String sql2 = "delete from login where login_name=?";
            //执行SQL传入参数，并获取受影响行数
            result = exeUpdate(sql, loginName );
            result = exeUpdate(sql2, loginName );
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            //关闭所有连接
            closeAll();
        }
        return result;
    }

    /**
     * 更新成员 根据loginname
     * @param member
     * @return
     */
    public int updateMember(Member member) {
        int result = 0;
        try {
            //获取连接
            getCon();
            // [MODIFIED] 要执行的更新SQL, 添加 manager_id=?
            String sql = "update member SET member_name=?,member_gender=?,member_grade=?,member_institute=?,member_add=?,member_email=?,member_phone=?,creat_community=?,join_communityId=?, manager_id=? where login_name=?";
            // [MODIFIED] 执行SQL传入参数，并获取手影响行数, 添加 member.getManagerId()
            result = exeUpdate(sql,
                    new Object[] { member.getMemberName(), member.getMemberGender(),
                            member.getMemberGrade(), member.getMemberInstitute(), member.getMemberAdd(),
                            member.getMemberEmail(), member.getMemberPhone(), member.getIscreatCommunity(),
                            member.getIsJoinCommunity(), member.getManagerId(), member.getLoginName() });
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            //关闭所有连接
            closeAll();
        }
        return result;
    }

    /**
     * 根据成员名获取登录名
     * @param membername
     * @return
     */
    public String getLoginNameByName(String membername) {
        String loginName = null;
        try {
            //获取连接
            getCon();
            //要执行的查询SQL，
            String sql = "select login_name from member where member_name=?";
            //执行SQL并传入参数
            exeQuery(sql, membername);
            //获取结果集
            ResultSet rs = (ResultSet) ps.executeQuery();
            //如果结果集存在，获取对应loginName字段
            while (rs.next()) {
                loginName = rs.getString(1);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            //关闭所有连接
            closeAll();
        }
        return loginName;
    }

    /**
     * 未搜索框获取成员列表 (系统管理员使用)
     * @param membername  模糊字段 (可匹配 姓名 或 用户名)
     * @param page 分页信息
     * @return
     */
    public List<Member> getMemberForSerach(String membername, Page page) {
        List<Member> members = new ArrayList<Member>();

        try {
            //获取连接
            getCon();
            // [FIXED] 修改SQL以同时搜索 member_name 和 login_name
            String countsql = "select count(1) from member as m where (m.member_name like ? OR m.login_name like ?)";

            //执行SQL 并传入参数
            exeQuery(countsql, "%" + membername + "%", "%" + membername + "%");
            //获取结果集
            ResultSet countResultSet = (ResultSet) ps.executeQuery();
            // 如果存在结果，将其赋值给itemCont
            if (countResultSet.next()) {
                int count = countResultSet.getInt(1);
                page.setItemCont(count);
            }
            //要执行的查询的SQL
            // [FIXED] 修改SQL以同时搜索 member_name 和 login_name, 并保持LEFT JOIN
            String sql = "select m.login_name, m.member_name,m.member_gender,m.manager_id,m.member_grade,m.member_institute,m.member_phone,m.member_email, c.community_name "
                    + "from member as m "
                    + "LEFT JOIN community as c ON m.join_communityId=c.community_id "
                    + "where (m.member_name like ? OR m.login_name like ?) limit ?,?";

            //执行SQL 并传入参数
            exeQuery(sql, "%" + membername + "%", "%" + membername + "%", page.getItemStart(), page.getPageSize());
            //获取结果集
            ResultSet rs = (ResultSet) ps.executeQuery();
            //如果结果集不为空，获取相应字段，赋值给一个新的member对象，并将其添加到members集合
            while (rs.next()) {
                Member member = new Member();
                member.setLoginName(rs.getString("login_name"));
                member.setMemberName(rs.getString("member_name"));
                member.setMemberGender(rs.getString("member_gender"));
                member.setManagerId(rs.getInt("manager_id"));
                member.setMemberGrade(rs.getString("member_grade"));
                member.setMemberInstitute(rs.getString("member_institute"));
                member.setMemberPhone(rs.getString("member_phone"));
                member.setMemberEmail(rs.getString("member_email"));
                member.setJoinCommunity(rs.getString("community_name"));

                //将member对象添加到members集合中
                members.add(member);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            //关闭所有连接
            closeAll();
        }
        return members;
    }

    /**
     * [NEW] 为社团管理员的搜索框获取成员列表 (社团管理员使用)
     * @param membername  模糊字段 (可匹配 姓名 或 用户名)
     * @param page 分页信息
     * @param loginName   社团管理员的登录名
     * @return
     */
    public List<Member> getMemberForSerach(String membername, Page page, String loginName) {
        List<Member> members = new ArrayList<Member>();

        try {
            //获取连接
            getCon();
            // 为page分页，查询总记录数
            // [FIXED] 修改SQL以同时搜索 member_name 和 login_name
            String countsql = "select count(1) from member as m " +
                    "where (m.member_name like ? OR m.login_name like ?) and m.join_communityId IN (select join_communityId from member as m1 where m1.login_name = ?)";

            //执行SQL 并传入参数
            exeQuery(countsql, "%" + membername + "%", "%" + membername + "%", loginName);
            //获取结果集
            ResultSet countResultSet = (ResultSet) ps.executeQuery();
            // 如果存在结果，将其赋值给itemCont
            if (countResultSet.next()) {
                int count = countResultSet.getInt(1);
                page.setItemCont(count);
            }

            //要执行的查询的SQL
            // [FIXED] 修改SQL以同时搜索 member_name 和 login_name, 并保持LEFT JOIN
            String sql = "select m.login_name, m.member_name,m.member_gender,m.manager_id,m.member_grade,m.member_institute,m.member_phone,m.member_email, c.community_name "
                    + "from member as m "
                    + "LEFT JOIN community as c ON m.join_communityId=c.community_id "
                    + "where (m.member_name like ? OR m.login_name like ?) and m.join_communityId IN (select join_communityId from member as m1 where m1.login_name = ?) limit ?,?";

            //执行SQL 并传入参数
            exeQuery(sql, "%" + membername + "%", "%" + membername + "%", loginName, page.getItemStart(), page.getPageSize());
            //获取结果集
            ResultSet rs = (ResultSet) ps.executeQuery();
            //如果结果集不为空，获取相应字段，赋值给一个新的member对象，并将其添加到members集合
            while (rs.next()) {
                Member member = new Member();
                member.setLoginName(rs.getString("login_name"));
                member.setMemberName(rs.getString("member_name"));
                member.setMemberGender(rs.getString("member_gender"));
                member.setManagerId(rs.getInt("manager_id"));
                member.setMemberGrade(rs.getString("member_grade"));
                member.setMemberInstitute(rs.getString("member_institute"));
                member.setMemberPhone(rs.getString("member_phone"));
                member.setMemberEmail(rs.getString("member_email"));
                member.setJoinCommunity(rs.getString("community_name"));
                //将member对象添加到members集合中
                members.add(member);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            //关闭所有连接
            closeAll();
        }
        return members;
    }

}